﻿using ClassLibrary;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using UboxUI;
using User.Properties;

namespace User
{
    public partial class FrmUserControl : FrmFather
    {
        public FrmUserControl()
        {
            InitializeComponent();

            #region 視窗初始化

            this.LblTitle.Text = this.Text;
            this.LblTitle2.Text = LblTitle.Text;
            this.BtnClose.Left = this.Width - this.BtnClose.Width - 10;
            this.BtnNarrow.Left = this.BtnClose.Left - this.BtnNarrow.Width - 10;
            this.panel1.Top = 0;
            this.panel2.Top = this.panel1.Height;

            #endregion

        }

        private void FrmUserControl_Load(object sender, EventArgs e)
        {
            LvUserdata.View = View.Details;

            CboSex.DropDownStyle = ComboBoxStyle.DropDownList;

            CboJailsetday.SelectedIndex = 0;

            this.CboPaging.SelectedIndex = 0;

            #region 註冊日期選單設定

            DtpRegistrationdate1.ShowCheckBox = true;
            DtpRegistrationdate1.Checked = false;
            DtpRegistrationdate2.ShowCheckBox = true;
            DtpRegistrationdate2.Checked = false;

            DtpBirthday1.ShowCheckBox = true;
            DtpBirthday1.Checked = false;
            DtpBirthday2.ShowCheckBox = true;
            DtpBirthday2.Checked = false;

            DtpJaildate1.ShowCheckBox = true;
            DtpJaildate1.Checked = false;
            DtpJaildate2.ShowCheckBox = true;
            DtpJaildate2.Checked = false;

            #endregion
        }

        #region 日期選單設定

        private void DtpBirthday1_ValueChanged(object sender, EventArgs e)
        {
            if ((this.DtpBirthday2.Value < this.DtpBirthday1.Value) && (this.DtpBirthday2.Checked == true))
            {
                this.DtpBirthday1.Value = this.DtpBirthday2.Value.AddDays(-1);
                MessageBox.Show("日期不可比結束日期之後");
            }
        }

        private void DtpBirthday2_ValueChanged(object sender, EventArgs e)
        {
            if ((this.DtpBirthday2.Value < this.DtpBirthday1.Value) && (this.DtpBirthday1.Checked == true))
            {
                this.DtpBirthday2.Value = this.DtpBirthday1.Value.AddDays(1);
                MessageBox.Show("日期不可比起始日期更之前");
            }
        }

        private void DtpJaildate1_ValueChanged(object sender, EventArgs e)
        {
            if ((this.DtpJaildate2.Value < this.DtpJaildate1.Value) && (this.DtpJaildate2.Checked == true))
            {
                this.DtpJaildate1.Value = this.DtpJaildate2.Value.AddDays(-1);
                MessageBox.Show("日期不可比結束日期之後");
            }
        }

        private void DtpJaildate2_ValueChanged(object sender, EventArgs e)
        {
            if ((this.DtpJaildate2.Value < this.DtpJaildate1.Value) && (this.DtpJaildate1.Checked == true))
            {
                this.DtpJaildate2.Value = this.DtpJaildate1.Value.AddDays(1);
                MessageBox.Show("日期不可比起始日期更之前");
            }
        }

        private void DtpRegistrationdate1_ValueChanged(object sender, EventArgs e)
        {
            if ((this.DtpRegistrationdate2.Value < this.DtpRegistrationdate1.Value) && (this.DtpRegistrationdate2.Checked == true))
            {
                this.DtpRegistrationdate1.Value = this.DtpRegistrationdate2.Value.AddDays(-1);
                MessageBox.Show("日期不可比結束日期之後");
            }
        }

        private void DtpRegistrationdate2_ValueChanged(object sender, EventArgs e)
        {
            if ((this.DtpRegistrationdate2.Value < this.DtpRegistrationdate1.Value) && (this.DtpRegistrationdate1.Checked == true))
            {
                this.DtpRegistrationdate2.Value = this.DtpRegistrationdate1.Value.AddDays(1);
                MessageBox.Show("日期不可比起始日期更之前");
            }
        }

        #endregion

        #region SQL
        string strsqlPaging = @"
                               select count(*) from [UBOX].[dbo].[user] ";
        string strsqlChange;

        bool CbopagenumChangedCheck = true;
        #endregion

        private void BtnSelect_Click(object sender, EventArgs e)
        {
            CbopagenumChangedCheck = false;
            int selectnum = 0;
            string strsqlPagingpiece = @"
                               select count(*) from [UBOX].[dbo].[user] ";
            LvUserdata.Items.Clear();
            #region SQL 取得管理者資料
            string strsql = @"
                               select user_id,
                                      user_username,
                                      user_first_name,
                                      user_last_name,
                                      user_sex,
                                      user_birthday,
                                      user_mobile_phone,
                                      user_email,
                                      user_jail_date,
                                      user_registration_date
                               from [UBOX].[dbo].[user] 
                            ";
            string strsqlpiece = "";
            #region 條件判斷

            if (
                (TxtUsername.Text != "") ||
                (TxtFirstname.Text != "") ||
                (TxtLastname.Text != "") ||
                (TxtMobilephone.Text != "") ||
                (TxtEmail.Text != "") ||
                ((CboSex.SelectedIndex != -1) && (CboSex.SelectedIndex != 2)) ||
                (DtpBirthday1.Checked == true) ||
                (DtpBirthday2.Checked == true) ||
                ((DtpJaildate1.Checked == true) && (RdoFreeze.Checked == true)) ||
                ((DtpJaildate2.Checked == true)&&(RdoFreeze.Checked == true))||
                ((RdoFreeze.Checked == true)&&(DtpJaildate2.Checked == false)&&(DtpJaildate1.Checked == false))||
                (RdoThaw.Checked==true)||
                (RdoThawexpired.Checked == true) ||
                (DtpRegistrationdate1.Checked == true) ||
                (DtpRegistrationdate2.Checked == true)
                )
            {
                strsqlpiece += " WHERE ";
            }
            #region 其它項判斷
            if (TxtUsername.Text != "")
            {
                strsqlpiece += " user_username like('%'+@Username+'%')";
                selectnum++;
            }
            if (TxtFirstname.Text != "")
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += " user_first_name like('%'+@Firstname+'%')";
                selectnum++;
            }
            if (TxtLastname.Text != "")
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += " user_last_name like('%'+@Lastname+'%')";
                selectnum++;
            }
            if ((CboSex.SelectedIndex != -1) && (CboSex.SelectedIndex != 2))
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += " user_sex like('%'+@Sex+'%')";
                selectnum++;
            }

            if (TxtMobilephone.Text != "")
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += " user_mobile_phone like('%'+@Mobilephone+'%')";
                selectnum++;
            }

            if (TxtEmail.Text != "")
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += " user_email like('%'+@Email+'%')";
                selectnum++;
            }

            #endregion

            #region 日期選單判斷

            if ((DtpBirthday1.Checked == true) || (DtpBirthday2.Checked == true))
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += "user_birthday BETWEEN @Birthday1  AND  @Birthday2";
                selectnum++;
            }

            if (((DtpJaildate1.Checked == true) || (DtpJaildate2.Checked == true))&&(RdoFreeze.Checked==true))
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += "user_jail_date BETWEEN @Jaildate1  AND  @Jaildate2";
                selectnum++;
            }
            else if (RdoFreeze.Checked == true)
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += "user_jail_date is not NULL";
                selectnum++;
            }
            if ((DtpRegistrationdate1.Checked == true) || (DtpRegistrationdate2.Checked == true))
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += "user_registration_date BETWEEN @Registration_date1  AND  @Registration_date2";
                selectnum++;
            }
            if (RdoThaw.Checked == true)
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += "user_jail_date is NULL";
                selectnum++;
            }
            if (RdoThawexpired.Checked== true)
            {
                if (selectnum > 0)
                {
                    strsqlpiece += " AND ";
                }
                strsqlpiece += "user_jail_date <@Jaildate";
                selectnum++;
            }
            #endregion

            #region 排序判斷
            int Sortnum = 0;
            string strsqlpiece2="";
            if((RdoAsc.Checked==true)||(RdoDesc.Checked==true))
            {
                foreach(ListViewItem Item in LvSort.Items)
                {
                    if (Item.Checked == true) 
                    {
                        if (Sortnum == 0)
                        {
                            strsqlpiece2 += " order by ";
                        }
                        else 
                        {
                            strsqlpiece2 += ", ";
                        }
                        strsqlpiece2 += Item.Tag + " ";
                        if (RdoAsc.Checked == true)
                        {
                            strsqlpiece2 += " ASC";
                        }
                        else
                        {
                            strsqlpiece2 += " DESC";
                        }
                        Sortnum++;
                    }
                }
            }
            if (Sortnum == 0)
            {
                strsqlpiece2 += " order by user_username ASC";
            }
            #endregion
            strsqlChange = strsql + strsqlpiece + strsqlpiece2;
            strsql = strsql + strsqlpiece + strsqlpiece2 + " OFFSET 0 ROWS FETCH NEXT @Shownum ROWS ONLY";
            strsqlPaging = strsqlPagingpiece + strsqlpiece;
            #endregion

            #endregion

            #region 連線設定

            string strconn = login.strconn;
            SqlConnection conn = new SqlConnection(strconn);

            #endregion

            #region cmd設定
            SqlCommand cmd;
            cmd = new SqlCommand(strsql, conn);
            cmd.CommandType = CommandType.Text;
            #endregion

            #region Input變數

            #region 其它參數

            if (TxtUsername.Text != "")
            {
                SqlParameter Username = new SqlParameter("@Username", SqlDbType.NVarChar, 20);
                Username.Direction = ParameterDirection.Input;
                Username.Value = this.TxtUsername.Text;
                cmd.Parameters.Add(Username);
            }
            if (TxtFirstname.Text != "")
            {
                SqlParameter Firstname = new SqlParameter("@Firstname", SqlDbType.NVarChar, 10);
                Firstname.Direction = ParameterDirection.Input;
                Firstname.Value = this.TxtFirstname.Text;
                cmd.Parameters.Add(Firstname);
            }
            if (TxtLastname.Text != "")
            {
                SqlParameter Lastname = new SqlParameter("@Lastname", SqlDbType.NVarChar, 10);
                Lastname.Direction = ParameterDirection.Input;
                Lastname.Value = this.TxtLastname.Text;
                cmd.Parameters.Add(Lastname);
            }

            if ((CboSex.SelectedIndex != -1)&&(CboSex.SelectedIndex != 2))
            {
                SqlParameter Sex = new SqlParameter("@Sex", SqlDbType.Char, 1);
                Sex.Direction = ParameterDirection.Input;
                if (CboSex.SelectedIndex == 0)
                {
                    Sex.Value = "F";
                }
                else 
                {
                    Sex.Value = "M";
                }
                cmd.Parameters.Add(Sex);
            }

            if (TxtMobilephone.Text != "")
            {
                SqlParameter Mobilephone = new SqlParameter("@Mobilephone", SqlDbType.NVarChar, 10);
                Mobilephone.Direction = ParameterDirection.Input;
                Mobilephone.Value = this.TxtMobilephone.Text;
                cmd.Parameters.Add(Mobilephone);
            }

            if (TxtEmail.Text != "")
            {
                SqlParameter Email = new SqlParameter("@Email", SqlDbType.NVarChar, 30);
                Email.Direction = ParameterDirection.Input;
                Email.Value = this.TxtEmail.Text;
                cmd.Parameters.Add(Email);
            }

            SqlParameter Shownum = new SqlParameter("@Shownum", SqlDbType.Int);
            Shownum.Direction = ParameterDirection.Input;
            Shownum.Value = this.CboPaging.SelectedItem.ToString();
            cmd.Parameters.Add(Shownum);

            #endregion

            #region Birthday日期參數

            if ((DtpBirthday1.Checked == true) || (DtpBirthday2.Checked == true))
            {
                SqlParameter Birthday1 = new SqlParameter("@Birthday1", SqlDbType.DateTime, 20);
                Birthday1.Direction = ParameterDirection.Input;
                SqlParameter Birthday2 = new SqlParameter("@Birthday2", SqlDbType.DateTime, 20);
                Birthday2.Direction = ParameterDirection.Input;
                if ((DtpBirthday1.Checked == true) && (DtpBirthday2.Checked == true))
                {
                    Birthday1.Value =
                        string.Format(
                        "{0}-{1}-{2}",
                        DtpBirthday1.Value.Year,
                        DtpBirthday1.Value.Month,
                        DtpBirthday1.Value.Day
                        );
                    Birthday2.Value =
                        string.Format(
                        "{0}-{1}-{2}",
                        DtpBirthday2.Value.Year,
                        DtpBirthday2.Value.Month,
                        DtpBirthday2.Value.Day
                        );
                }
                else if ((DtpBirthday1.Checked == true))
                {
                    Birthday1.Value =
                        string.Format(
                        "{0}-{1}-{2}",
                        DtpBirthday1.Value.Year,
                        DtpBirthday1.Value.Month,
                        DtpBirthday1.Value.Day
                        );
                    Birthday2.Value = Birthday1.Value;
                }
                else if ((DtpBirthday2.Checked == true))
                {
                    Birthday1.Value =
                      string.Format(
                      "{0}-{1}-{2}",
                      DtpBirthday2.Value.Year,
                      DtpBirthday2.Value.Month,
                      DtpBirthday2.Value.Day
                      );
                    Birthday2.Value = Birthday1.Value;
                }
                cmd.Parameters.Add(Birthday1);
                cmd.Parameters.Add(Birthday2);
            }

            #endregion

            #region DtpJaildate日期參數

            if (RdoThawexpired.Checked == true)
            {
                SqlParameter Jaildate = new SqlParameter("@Jaildate", SqlDbType.DateTime, 20);
                Jaildate.Direction = ParameterDirection.Input;
                Jaildate.Value = DateTime.Now;
                cmd.Parameters.Add(Jaildate);
            }

            if ((DtpJaildate1.Checked == true) || (DtpJaildate2.Checked == true))
            {
                SqlParameter Jaildate1 = new SqlParameter("@Jaildate1", SqlDbType.DateTime, 20);
                Jaildate1.Direction = ParameterDirection.Input;
                SqlParameter Jaildate2 = new SqlParameter("@Jaildate2", SqlDbType.DateTime, 20);
                Jaildate2.Direction = ParameterDirection.Input;
                if ((DtpJaildate1.Checked == true) && (DtpJaildate2.Checked == true))
                {
                    Jaildate1.Value =
                        string.Format(
                        "{0}-{1}-{2} 00:00:00.000",
                        DtpJaildate1.Value.Year,
                        DtpJaildate1.Value.Month,
                        DtpJaildate1.Value.Day
                        );
                    Jaildate2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpJaildate2.Value.Year,
                        DtpJaildate2.Value.Month,
                        DtpJaildate2.Value.Day
                        );
                }
                else if ((DtpJaildate1.Checked == true))
                {
                    Jaildate1.Value =
                        string.Format(
                        "{0}-{1}-{2} 00:00:00.000",
                        DtpJaildate1.Value.Year,
                        DtpJaildate1.Value.Month,
                        DtpJaildate1.Value.Day
                        );
                    Jaildate2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpJaildate1.Value.Year,
                        DtpJaildate1.Value.Month,
                        DtpJaildate1.Value.Day
                        );
                }
                else if ((DtpJaildate2.Checked == true))
                {
                    Jaildate1.Value =
                      string.Format(
                      "{0}-{1}-{2} 00:00:00.000",
                      DtpJaildate2.Value.Year,
                      DtpJaildate2.Value.Month,
                      DtpJaildate2.Value.Day
                      );
                    Jaildate2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpJaildate2.Value.Year,
                        DtpJaildate2.Value.Month,
                        DtpJaildate2.Value.Day
                        );
                }
                cmd.Parameters.Add(Jaildate1);
                cmd.Parameters.Add(Jaildate2);
            }

            #endregion

            #region Registrationdate日期參數

            if ((DtpRegistrationdate1.Checked == true) || (DtpRegistrationdate2.Checked == true))
            {
                SqlParameter Registration_date1 = new SqlParameter("@Registration_date1", SqlDbType.DateTime, 20);
                Registration_date1.Direction = ParameterDirection.Input;
                SqlParameter Registration_date2 = new SqlParameter("@Registration_date2", SqlDbType.DateTime, 20);
                Registration_date2.Direction = ParameterDirection.Input;
                if ((DtpRegistrationdate1.Checked == true) && (DtpRegistrationdate2.Checked == true))
                {
                    Registration_date1.Value =
                        string.Format(
                        "{0}-{1}-{2} 00:00:00.000",
                        DtpRegistrationdate1.Value.Year,
                        DtpRegistrationdate1.Value.Month,
                        DtpRegistrationdate1.Value.Day
                        );
                    Registration_date2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpRegistrationdate2.Value.Year,
                        DtpRegistrationdate2.Value.Month,
                        DtpRegistrationdate2.Value.Day
                        );   
                }
                else if ((DtpRegistrationdate1.Checked == true))
                {
                    Registration_date1.Value =
                        string.Format(
                        "{0}-{1}-{2} 00:00:00.000",
                        DtpRegistrationdate1.Value.Year,
                        DtpRegistrationdate1.Value.Month,
                        DtpRegistrationdate1.Value.Day
                        );
                    Registration_date2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpRegistrationdate1.Value.Year,
                        DtpRegistrationdate1.Value.Month,
                        DtpRegistrationdate1.Value.Day
                        );
                }
                else if ((DtpRegistrationdate2.Checked == true))
                {
                    Registration_date1.Value =
                      string.Format(
                      "{0}-{1}-{2} 00:00:00.000",
                      DtpRegistrationdate2.Value.Year,
                      DtpRegistrationdate2.Value.Month,
                      DtpRegistrationdate2.Value.Day
                      );
                    Registration_date2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpRegistrationdate2.Value.Year,
                        DtpRegistrationdate2.Value.Month,
                        DtpRegistrationdate2.Value.Day
                        );
                }
                cmd.Parameters.Add(Registration_date1);
                cmd.Parameters.Add(Registration_date2);
            }
        
            #endregion

            #endregion

            #region 執行
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            int i = 1;
            while (dr.Read())
            {
                #region 顯示轉換
                string sexstring = "";
                switch (Convert.ToString(dr["user_sex"]))
                {
                    case "F":
                        sexstring = "女";
                        break;
                    case "M":
                        sexstring = "男";
                        break;
                }
                string user_birthday_string = "";
                if (!dr.IsDBNull(dr.GetOrdinal("user_birthday")))
                {
                    user_birthday_string = Convert.ToDateTime(dr["user_birthday"]).ToString("yyyy-MM-dd");
                }

                string user_jail_date_string = "";
                if (!dr.IsDBNull(dr.GetOrdinal("user_jail_date")))
                {
                    DateTime user_jail_date = Convert.ToDateTime(dr["user_jail_date"]);
                    user_jail_date_string =
                    string.Format(user_jail_date.ToString("yyyy-MM-dd HH:mm"));
                }
                DateTime user_registration_date = Convert.ToDateTime(dr["user_registration_date"]);
                string user_registration_date_string =
                string.Format(user_registration_date.ToString("yyyy-MM-dd HH:mm"));
                #endregion
                string[] list = {
                                   i.ToString(),
                                   Convert.ToString(dr["user_username"]),
                                   Convert.ToString(dr["user_first_name"]),
                                   Convert.ToString(dr["user_last_name"]),
                                   sexstring,
                                   user_birthday_string,
                                   Convert.ToString(dr["user_mobile_phone"]),
                                   Convert.ToString(dr["user_email"]),
                                   user_jail_date_string,
                                   user_registration_date_string
                               };
                ListViewItem Item = new System.Windows.Forms.ListViewItem(list, -1);
                Item.Tag = Convert.ToString(dr["user_id"]);
                LvUserdata.Items.Add(Item);

                i++;
            }
            cmd.Dispose();
            conn.Close();
            #endregion

            cmd = new SqlCommand(strsqlPaging, conn);
            #region Input變數

            #region 其它參數

            if (TxtUsername.Text != "")
            {
                SqlParameter Username = new SqlParameter("@Username", SqlDbType.NVarChar, 20);
                Username.Direction = ParameterDirection.Input;
                Username.Value = this.TxtUsername.Text;
                cmd.Parameters.Add(Username);
            }
            if (TxtFirstname.Text != "")
            {
                SqlParameter Firstname = new SqlParameter("@Firstname", SqlDbType.NVarChar, 10);
                Firstname.Direction = ParameterDirection.Input;
                Firstname.Value = this.TxtFirstname.Text;
                cmd.Parameters.Add(Firstname);
            }
            if (TxtLastname.Text != "")
            {
                SqlParameter Lastname = new SqlParameter("@Lastname", SqlDbType.NVarChar, 10);
                Lastname.Direction = ParameterDirection.Input;
                Lastname.Value = this.TxtLastname.Text;
                cmd.Parameters.Add(Lastname);
            }

            if ((CboSex.SelectedIndex != -1) && (CboSex.SelectedIndex != 2))
            {
                SqlParameter Sex = new SqlParameter("@Sex", SqlDbType.Char, 1);
                Sex.Direction = ParameterDirection.Input;
                if (CboSex.SelectedIndex == 0)
                {
                    Sex.Value = "F";
                }
                else
                {
                    Sex.Value = "M";
                }
                cmd.Parameters.Add(Sex);
            }

            if (TxtMobilephone.Text != "")
            {
                SqlParameter Mobilephone = new SqlParameter("@Mobilephone", SqlDbType.NVarChar, 10);
                Mobilephone.Direction = ParameterDirection.Input;
                Mobilephone.Value = this.TxtMobilephone.Text;
                cmd.Parameters.Add(Mobilephone);
            }

            if (TxtEmail.Text != "")
            {
                SqlParameter Email = new SqlParameter("@Email", SqlDbType.NVarChar, 30);
                Email.Direction = ParameterDirection.Input;
                Email.Value = this.TxtEmail.Text;
                cmd.Parameters.Add(Email);
            }

            SqlParameter Shownum2 = new SqlParameter("@Shownum", SqlDbType.Int);
            Shownum2.Direction = ParameterDirection.Input;
            Shownum2.Value = this.CboPaging.SelectedItem.ToString();
            cmd.Parameters.Add(Shownum2);

            #endregion

            #region Birthday日期參數

            if ((DtpBirthday1.Checked == true) || (DtpBirthday2.Checked == true))
            {
                SqlParameter Birthday1 = new SqlParameter("@Birthday1", SqlDbType.DateTime, 20);
                Birthday1.Direction = ParameterDirection.Input;
                SqlParameter Birthday2 = new SqlParameter("@Birthday2", SqlDbType.DateTime, 20);
                Birthday2.Direction = ParameterDirection.Input;
                if ((DtpBirthday1.Checked == true) && (DtpBirthday2.Checked == true))
                {
                    Birthday1.Value =
                        string.Format(
                        "{0}-{1}-{2}",
                        DtpBirthday1.Value.Year,
                        DtpBirthday1.Value.Month,
                        DtpBirthday1.Value.Day
                        );
                    Birthday2.Value =
                        string.Format(
                        "{0}-{1}-{2}",
                        DtpBirthday2.Value.Year,
                        DtpBirthday2.Value.Month,
                        DtpBirthday2.Value.Day
                        );
                }
                else if ((DtpBirthday1.Checked == true))
                {
                    Birthday1.Value =
                        string.Format(
                        "{0}-{1}-{2}",
                        DtpBirthday1.Value.Year,
                        DtpBirthday1.Value.Month,
                        DtpBirthday1.Value.Day
                        );
                    Birthday2.Value = Birthday1.Value;
                }
                else if ((DtpBirthday2.Checked == true))
                {
                    Birthday1.Value =
                      string.Format(
                      "{0}-{1}-{2}",
                      DtpBirthday2.Value.Year,
                      DtpBirthday2.Value.Month,
                      DtpBirthday2.Value.Day
                      );
                    Birthday2.Value = Birthday1.Value;
                }
                cmd.Parameters.Add(Birthday1);
                cmd.Parameters.Add(Birthday2);
            }

            #endregion

            #region DtpJaildate日期參數

            if (RdoThawexpired.Checked == true)
            {
                SqlParameter Jaildate = new SqlParameter("@Jaildate", SqlDbType.DateTime, 20);
                Jaildate.Direction = ParameterDirection.Input;
                Jaildate.Value = DateTime.Now;
                cmd.Parameters.Add(Jaildate);
            }

            if ((DtpJaildate1.Checked == true) || (DtpJaildate2.Checked == true))
            {
                SqlParameter Jaildate1 = new SqlParameter("@Jaildate1", SqlDbType.DateTime, 20);
                Jaildate1.Direction = ParameterDirection.Input;
                SqlParameter Jaildate2 = new SqlParameter("@Jaildate2", SqlDbType.DateTime, 20);
                Jaildate2.Direction = ParameterDirection.Input;
                if ((DtpJaildate1.Checked == true) && (DtpJaildate2.Checked == true))
                {
                    Jaildate1.Value =
                        string.Format(
                        "{0}-{1}-{2} 00:00:00.000",
                        DtpJaildate1.Value.Year,
                        DtpJaildate1.Value.Month,
                        DtpJaildate1.Value.Day
                        );
                    Jaildate2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpJaildate2.Value.Year,
                        DtpJaildate2.Value.Month,
                        DtpJaildate2.Value.Day
                        );
                }
                else if ((DtpJaildate1.Checked == true))
                {
                    Jaildate1.Value =
                        string.Format(
                        "{0}-{1}-{2} 00:00:00.000",
                        DtpJaildate1.Value.Year,
                        DtpJaildate1.Value.Month,
                        DtpJaildate1.Value.Day
                        );
                    Jaildate2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpJaildate1.Value.Year,
                        DtpJaildate1.Value.Month,
                        DtpJaildate1.Value.Day
                        );
                }
                else if ((DtpJaildate2.Checked == true))
                {
                    Jaildate1.Value =
                      string.Format(
                      "{0}-{1}-{2} 00:00:00.000",
                      DtpJaildate2.Value.Year,
                      DtpJaildate2.Value.Month,
                      DtpJaildate2.Value.Day
                      );
                    Jaildate2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpJaildate2.Value.Year,
                        DtpJaildate2.Value.Month,
                        DtpJaildate2.Value.Day
                        );
                }
                cmd.Parameters.Add(Jaildate1);
                cmd.Parameters.Add(Jaildate2);
            }

            #endregion

            #region Registrationdate日期參數

            if ((DtpRegistrationdate1.Checked == true) || (DtpRegistrationdate2.Checked == true))
            {
                SqlParameter Registration_date1 = new SqlParameter("@Registration_date1", SqlDbType.DateTime, 20);
                Registration_date1.Direction = ParameterDirection.Input;
                SqlParameter Registration_date2 = new SqlParameter("@Registration_date2", SqlDbType.DateTime, 20);
                Registration_date2.Direction = ParameterDirection.Input;
                if ((DtpRegistrationdate1.Checked == true) && (DtpRegistrationdate2.Checked == true))
                {
                    Registration_date1.Value =
                        string.Format(
                        "{0}-{1}-{2} 00:00:00.000",
                        DtpRegistrationdate1.Value.Year,
                        DtpRegistrationdate1.Value.Month,
                        DtpRegistrationdate1.Value.Day
                        );
                    Registration_date2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpRegistrationdate2.Value.Year,
                        DtpRegistrationdate2.Value.Month,
                        DtpRegistrationdate2.Value.Day
                        );
                }
                else if ((DtpRegistrationdate1.Checked == true))
                {
                    Registration_date1.Value =
                        string.Format(
                        "{0}-{1}-{2} 00:00:00.000",
                        DtpRegistrationdate1.Value.Year,
                        DtpRegistrationdate1.Value.Month,
                        DtpRegistrationdate1.Value.Day
                        );
                    Registration_date2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpRegistrationdate1.Value.Year,
                        DtpRegistrationdate1.Value.Month,
                        DtpRegistrationdate1.Value.Day
                        );
                }
                else if ((DtpRegistrationdate2.Checked == true))
                {
                    Registration_date1.Value =
                      string.Format(
                      "{0}-{1}-{2} 00:00:00.000",
                      DtpRegistrationdate2.Value.Year,
                      DtpRegistrationdate2.Value.Month,
                      DtpRegistrationdate2.Value.Day
                      );
                    Registration_date2.Value =
                        string.Format(
                        "{0}-{1}-{2} 23:59:59.999",
                        DtpRegistrationdate2.Value.Year,
                        DtpRegistrationdate2.Value.Month,
                        DtpRegistrationdate2.Value.Day
                        );
                }
                cmd.Parameters.Add(Registration_date1);
                cmd.Parameters.Add(Registration_date2);
            }

            #endregion

            #endregion

            #region 執行
            conn.Open();
            string num = cmd.ExecuteScalar().ToString();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            #endregion

            #region 分頁
            int pagenum = int.Parse(num) / Convert.ToInt32(CboPaging.SelectedItem.ToString());
            int pagenum2 = int.Parse(num) % Convert.ToInt32(CboPaging.SelectedItem.ToString());
            if (pagenum2 != 0)
            {
                pagenum = pagenum + 1;
            }
            if (pagenum == 0)
            {
                pagenum = 1;
            }
            Cbopagenum.Items.Clear();
            for (int i2 = 1; i2 <= pagenum; i2++)
            {
                Cbopagenum.Items.Add(i2);
            }
            Cbopagenum.SelectedIndex = 0;
            #endregion
            conn.Dispose();
            CbopagenumChangedCheck = true;
        }

        private void BtnFreeze_Click(object sender, EventArgs e)
        {
            #region 凍結確認視窗

            MessageBoxButtons buttons = MessageBoxButtons.OKCancel;
            MessageBoxIcon icon = MessageBoxIcon.Question;
            MessageBoxDefaultButton defaultButton = MessageBoxDefaultButton.Button2;
            DialogResult result;
            string m = "確定要凍結嗎";
            string c = "凍結確認";

            #endregion

            if(LvUserdata.CheckedItems.Count>0)
            {
                #region 凍結方法一

                if (RdoJail1.Checked == true)
                {
                    result = MessageBox.Show(m, c, buttons, icon, defaultButton);
                    if (result == DialogResult.OK)
                    {
                        try
                        {
                            #region SQL 凍結使用者
                            string strsql = @"
                                    update [UBOX].[dbo].[user] set user_jail_date=@Jail_date,[user_visible]=0 where ";

                            string Freezestring = " ";
                            int Freezenum = 0;
                            foreach (ListViewItem Item in LvUserdata.CheckedItems)
                            {
                                if (Freezenum > 0) { Freezestring = Freezestring + "or"; }
                                Freezestring = Freezestring + " user_id=" + Item.Tag + " ";
                                Freezenum++;
                            }
                            strsql += Freezestring;
                            strsql += " set @Check=@@ROWCOUNT ";
                            #endregion

                            #region 連線設定

                            string strconn = login.strconn;
                            SqlConnection conn = new SqlConnection(strconn);

                            #endregion

                            #region cmd設定
                            SqlCommand cmd;
                            cmd = new SqlCommand(strsql, conn);
                            cmd.CommandType = CommandType.Text;
                            #endregion

                            #region input變數
                            SqlParameter jail_date = new SqlParameter("@Jail_date", SqlDbType.DateTime);
                            jail_date.Direction = ParameterDirection.Input;
                            jail_date.Value = DtpJailsetdata.Value;
                            cmd.Parameters.Add(jail_date);
                            #endregion

                            #region Output變數
                            SqlParameter check = new SqlParameter("@Check", SqlDbType.Int, 1);
                            check.Direction = ParameterDirection.Output;
                            cmd.Parameters.Add(check);
                            #endregion

                            #region 執行
                            try
                            {
                                conn.Open();
                                cmd.ExecuteNonQuery();
                            }
                            catch
                            {
                                throw new Exception("凍結失敗");
                            }
                            cmd.Dispose();
                            conn.Close();
                            #endregion

                            #region 更新LvUserdata

                            LvUserdata.Items.Clear();
                            BtnSelect_Click(sender, e);
                            int checkDelete = Convert.ToInt32(cmd.Parameters["@Check"].Value);
                            if (checkDelete > 0)
                            {
                                throw new Exception("凍結成功");
                            }
                            else
                            {
                                throw new Exception("凍結失敗");
                            }

                            #endregion
                        }
                        catch (Exception ex)
                        {
                            MessageBox.Show(ex.Message);
                        }
                    }
                }

                #endregion

                #region 凍結方法二
                try
                {
                    if (RdoJail2.Checked == true)
                    {
                        int Successnum = 0;
                        int Failurenum = 0;
                        result = MessageBox.Show(m, c, buttons, icon, defaultButton);
                        #region 連線設定

                        string strconn = login.strconn;
                        SqlConnection conn = new SqlConnection(strconn);

                        #endregion
                        foreach (ListViewItem Item in LvUserdata.CheckedItems)
                        {
                            #region SQL 凍結使用者

                            string strsql = @"
                                    update [UBOX].[dbo].[user] set user_jail_date=@Jail_date, [user_visible]=0
                                    where user_id=@Id 
                                    set @Check=@@ROWCOUNT ";

                            #endregion

                            #region cmd設定
                            SqlCommand cmd;
                            cmd = new SqlCommand(strsql, conn);
                            cmd.CommandType = CommandType.Text;
                            #endregion

                            #region input變數
                            SqlParameter jail_date = new SqlParameter("@Jail_date", SqlDbType.DateTime);
                            jail_date.Direction = ParameterDirection.Input;
                            if (Item.SubItems[8].Text == "")
                            {
                                jail_date.Value = string.Format(
                                    "{0}-{1}-{2}",
                                    DateTime.Now.Year,
                                    DateTime.Now.Month,
                                    DateTime.Now.Day
                                    );
                            }
                            else
                            {
                                jail_date.Value = Convert.ToDateTime(Item.SubItems[8].Text);
                            }
                            switch (CboJailsetday.SelectedIndex)
                            {
                                case 0:
                                    jail_date.Value = Convert.ToDateTime(jail_date.Value).AddDays(3);
                                    break;
                                case 1:
                                    jail_date.Value = Convert.ToDateTime(jail_date.Value).AddDays(7);
                                    break;
                                case 2:
                                    jail_date.Value = Convert.ToDateTime(jail_date.Value).AddDays(15);
                                    break;
                                case 3:
                                    jail_date.Value = Convert.ToDateTime(jail_date.Value).AddDays(30);
                                    break;
                                case 4:
                                    jail_date.Value = Convert.ToDateTime(jail_date.Value).AddDays(-3);
                                    break;
                                case 5:
                                    jail_date.Value = Convert.ToDateTime(jail_date.Value).AddDays(-7);
                                    break;
                                case 6:
                                    jail_date.Value = Convert.ToDateTime(jail_date.Value).AddDays(-15);
                                    break;
                                case 7:
                                    jail_date.Value = Convert.ToDateTime(jail_date.Value).AddDays(-30);
                                    break;
                            }
                            cmd.Parameters.Add(jail_date);

                            SqlParameter Id = new SqlParameter("@Id", SqlDbType.Int);
                            Id.Direction = ParameterDirection.Input;
                            Id.Value = Item.Tag;
                            cmd.Parameters.Add(Id);

                            #endregion

                            #region Output變數
                            SqlParameter check = new SqlParameter("@Check", SqlDbType.Int, 1);
                            check.Direction = ParameterDirection.Output;
                            cmd.Parameters.Add(check);
                            #endregion

                            #region 執行
                            try
                            {
                                conn.Open();
                                cmd.ExecuteNonQuery();
                                Successnum++;
                            }
                            catch
                            {
                                Failurenum++;
                            }
                            cmd.Dispose();
                            conn.Close();
                            #endregion

                        }
                        MessageBox.Show(string.Format("凍結成功:{0}失敗:{1}", Successnum, Failurenum));
                        #region 更新LvUserdata

                        LvUserdata.Items.Clear();
                        BtnSelect_Click(sender, e);

                        #endregion

                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }


                #endregion
            }
        }

        private void BtnDelete_Click(object sender, EventArgs e)
        {
             #region 刪除確認視窗

            MessageBoxButtons buttons = MessageBoxButtons.OKCancel;
            MessageBoxIcon icon = MessageBoxIcon.Question;
            MessageBoxDefaultButton defaultButton = MessageBoxDefaultButton.Button2;
            DialogResult result;
            string m = "確定要刪除嗎";
            string c = "刪除確認";

            #endregion
            if (LvUserdata.CheckedItems.Count != 0)
            {
                result = MessageBox.Show(m, c, buttons, icon, defaultButton);
                if (result == DialogResult.OK)
                {
                    try
                    {
                        #region SQL 刪除使用者
                        string strsql = @"
                                    delete from  [UBOX].[dbo].[user] where ";
                        
                        string deletestring = " ";
                        int deletenum = 0;
                        foreach (ListViewItem Item in LvUserdata.CheckedItems)
                        {
                            if (deletenum > 0) { deletestring = deletestring + "or"; }
                            deletestring = deletestring + " user_id=" + Item.Tag + " ";
                            deletenum++;
                        }
                        strsql += deletestring;
                        strsql += " set @Check=@@ROWCOUNT ";
                        #endregion

                        #region 連線設定

                        string strconn = login.strconn;
                        SqlConnection conn = new SqlConnection(strconn);

                        #endregion

                        #region cmd設定
                        SqlCommand cmd;
                        cmd = new SqlCommand(strsql, conn);
                        cmd.CommandType = CommandType.Text;
                        #endregion

                        #region Output變數
                        SqlParameter check = new SqlParameter("@Check", SqlDbType.Int, 1);
                        check.Direction = ParameterDirection.Output;
                        cmd.Parameters.Add(check);
                        #endregion

                        #region 執行
                        try
                        {
                            conn.Open();
                            cmd.ExecuteNonQuery();
                        }
                        catch
                        {
                            throw new Exception("刪除失敗");
                        }
                        cmd.Dispose();
                        conn.Close();
                        #endregion

                        #region 更新LvUserdata

                        LvUserdata.Items.Clear();
                        BtnSelect_Click(sender, e);
                        int checkDelete = Convert.ToInt32(cmd.Parameters["@Check"].Value);
                        if (checkDelete > 0)
                        {
                            throw new Exception("刪除成功");
                        }
                        else
                        {
                            throw new Exception("刪除失敗");
                        }

                        #endregion
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }
        }

        private void btnThaw_Click(object sender, EventArgs e)
        {
            #region 解凍確認視窗

            MessageBoxButtons buttons = MessageBoxButtons.OKCancel;
            MessageBoxIcon icon = MessageBoxIcon.Question;
            MessageBoxDefaultButton defaultButton = MessageBoxDefaultButton.Button2;
            DialogResult result;
            string m = "確定要解凍嗎";
            string c = "解凍確認";

            #endregion
            if (LvUserdata.CheckedItems.Count != 0)
            {
                result = MessageBox.Show(m, c, buttons, icon, defaultButton);
                if (result == DialogResult.OK)
                {
                    try
                    {
                        #region SQL 凍結使用者
                        string strsql = @"
                                    update [UBOX].[dbo].[user] set user_jail_date=null,[user_visible]=1 where ";

                        string deletestring = " ";
                        int deletenum = 0;
                        foreach (ListViewItem Item in LvUserdata.CheckedItems)
                        {
                            if (deletenum > 0) { deletestring = deletestring + "or"; }
                            deletestring = deletestring + " user_id=" + Item.Tag + " ";
                            deletenum++;
                        }
                        strsql += deletestring;
                        strsql += " set @Check=@@ROWCOUNT ";
                        #endregion

                        #region 連線設定

                        string strconn = login.strconn;
                        SqlConnection conn = new SqlConnection(strconn);

                        #endregion

                        #region cmd設定
                        SqlCommand cmd;
                        cmd = new SqlCommand(strsql, conn);
                        cmd.CommandType = CommandType.Text;
                        #endregion

                        #region Output變數
                        SqlParameter check = new SqlParameter("@Check", SqlDbType.Int, 1);
                        check.Direction = ParameterDirection.Output;
                        cmd.Parameters.Add(check);
                        #endregion

                        #region 執行
                        try
                        {
                            conn.Open();
                            cmd.ExecuteNonQuery();
                        }
                        catch
                        {
                            throw new Exception("凍結失敗");
                        }
                        cmd.Dispose();
                        conn.Close();
                        #endregion

                        #region 更新LvUserdata

                        LvUserdata.Items.Clear();
                        BtnSelect_Click(sender, e);
                        int checkDelete = Convert.ToInt32(cmd.Parameters["@Check"].Value);
                        if (checkDelete > 0)
                        {
                            throw new Exception("凍結成功");
                        }
                        else
                        {
                            throw new Exception("凍結失敗");
                        }

                        #endregion
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }
            }
        }

        private void BtnUp_Click(object sender, EventArgs e)
        {
            if (LvSort.SelectedItems.Count > 0)
            {
                ListViewItem Item = LvSort.SelectedItems[0];
                int index;
                if (Item.Index > 0) { index = Item.Index - 1; }
                else { index = 0; }
                LvSort.Items.Remove(Item);
                LvSort.Items.Insert(index, Item);
                LvSort.Focus();
                Item.Focused = true;
            }
        }

        private void BtnDown_Click(object sender, EventArgs e)
        {
            if (LvSort.SelectedItems.Count > 0)
            {
                ListViewItem Item = LvSort.SelectedItems[0];
                int index;
                if (Item.Index < LvSort.Items.Count - 1) { index = Item.Index + 1; }
                else { index = Item.Index; }
                LvSort.Items.Remove(Item);
                LvSort.Items.Insert(index, Item);
                LvSort.Focus();
                Item.Focused = true;
            }
        }

        private void Cbopagenum_SelectedIndexChanged(object sender, EventArgs e)
        {
            
            if (CbopagenumChangedCheck==true)   
            {
                try
                {
                    string strsql = strsqlChange + " OFFSET @Cross ROWS FETCH NEXT @Shownum ROWS ONLY";
                    LvUserdata.Items.Clear();
                    #region 連線設定

                    string strconn = login.strconn;
                    SqlConnection conn = new SqlConnection(strconn);

                    #endregion

                    #region cmd設定
                    SqlCommand cmd;
                    cmd = new SqlCommand(strsql, conn);
                    cmd.CommandType = CommandType.Text;
                    #endregion

                    #region Input變數

                    #region 其它參數

                    if (TxtUsername.Text != "")
                    {
                        SqlParameter Username = new SqlParameter("@Username", SqlDbType.NVarChar, 20);
                        Username.Direction = ParameterDirection.Input;
                        Username.Value = this.TxtUsername.Text;
                        cmd.Parameters.Add(Username);
                    }
                    if (TxtFirstname.Text != "")
                    {
                        SqlParameter Firstname = new SqlParameter("@Firstname", SqlDbType.NVarChar, 10);
                        Firstname.Direction = ParameterDirection.Input;
                        Firstname.Value = this.TxtFirstname.Text;
                        cmd.Parameters.Add(Firstname);
                    }
                    if (TxtLastname.Text != "")
                    {
                        SqlParameter Lastname = new SqlParameter("@Lastname", SqlDbType.NVarChar, 10);
                        Lastname.Direction = ParameterDirection.Input;
                        Lastname.Value = this.TxtLastname.Text;
                        cmd.Parameters.Add(Lastname);
                    }

                    if ((CboSex.SelectedIndex != -1) && (CboSex.SelectedIndex != 2))
                    {
                        SqlParameter Sex = new SqlParameter("@Sex", SqlDbType.Char, 1);
                        Sex.Direction = ParameterDirection.Input;
                        if (CboSex.SelectedIndex == 0)
                        {
                            Sex.Value = "F";
                        }
                        else
                        {
                            Sex.Value = "M";
                        }
                        cmd.Parameters.Add(Sex);
                    }

                    if (TxtMobilephone.Text != "")
                    {
                        SqlParameter Mobilephone = new SqlParameter("@Mobilephone", SqlDbType.NVarChar, 10);
                        Mobilephone.Direction = ParameterDirection.Input;
                        Mobilephone.Value = this.TxtMobilephone.Text;
                        cmd.Parameters.Add(Mobilephone);
                    }

                    if (TxtEmail.Text != "")
                    {
                        SqlParameter Email = new SqlParameter("@Email", SqlDbType.NVarChar, 30);
                        Email.Direction = ParameterDirection.Input;
                        Email.Value = this.TxtEmail.Text;
                        cmd.Parameters.Add(Email);
                    }

                    SqlParameter Shownum = new SqlParameter("@Shownum", SqlDbType.Int);
                    Shownum.Direction = ParameterDirection.Input;
                    Shownum.Value = this.CboPaging.SelectedItem.ToString();
                    cmd.Parameters.Add(Shownum);

                    SqlParameter cross = new SqlParameter("@Cross", SqlDbType.Int);
                    cross.Direction = ParameterDirection.Input;
                    cross.Value = (Convert.ToInt32(this.CboPaging.SelectedItem) * (Convert.ToInt32(this.Cbopagenum.SelectedItem) - 1));
                    cmd.Parameters.Add(cross);

                    #endregion

                    #region Birthday日期參數

                    if ((DtpBirthday1.Checked == true) || (DtpBirthday2.Checked == true))
                    {
                        SqlParameter Birthday1 = new SqlParameter("@Birthday1", SqlDbType.DateTime, 20);
                        Birthday1.Direction = ParameterDirection.Input;
                        SqlParameter Birthday2 = new SqlParameter("@Birthday2", SqlDbType.DateTime, 20);
                        Birthday2.Direction = ParameterDirection.Input;
                        if ((DtpBirthday1.Checked == true) && (DtpBirthday2.Checked == true))
                        {
                            Birthday1.Value =
                                string.Format(
                                "{0}-{1}-{2}",
                                DtpBirthday1.Value.Year,
                                DtpBirthday1.Value.Month,
                                DtpBirthday1.Value.Day
                                );
                            Birthday2.Value =
                                string.Format(
                                "{0}-{1}-{2}",
                                DtpBirthday2.Value.Year,
                                DtpBirthday2.Value.Month,
                                DtpBirthday2.Value.Day
                                );
                        }
                        else if ((DtpBirthday1.Checked == true))
                        {
                            Birthday1.Value =
                                string.Format(
                                "{0}-{1}-{2}",
                                DtpBirthday1.Value.Year,
                                DtpBirthday1.Value.Month,
                                DtpBirthday1.Value.Day
                                );
                            Birthday2.Value = Birthday1.Value;
                        }
                        else if ((DtpBirthday2.Checked == true))
                        {
                            Birthday1.Value =
                              string.Format(
                              "{0}-{1}-{2}",
                              DtpBirthday2.Value.Year,
                              DtpBirthday2.Value.Month,
                              DtpBirthday2.Value.Day
                              );
                            Birthday2.Value = Birthday1.Value;
                        }
                        cmd.Parameters.Add(Birthday1);
                        cmd.Parameters.Add(Birthday2);
                    }

                    #endregion

                    #region DtpJaildate日期參數

                    if (RdoThawexpired.Checked == true)
                    {
                        SqlParameter Jaildate = new SqlParameter("@Jaildate", SqlDbType.DateTime, 20);
                        Jaildate.Direction = ParameterDirection.Input;
                        Jaildate.Value = DateTime.Now;
                        cmd.Parameters.Add(Jaildate);
                    }

                    if ((DtpJaildate1.Checked == true) || (DtpJaildate2.Checked == true))
                    {
                        SqlParameter Jaildate1 = new SqlParameter("@Jaildate1", SqlDbType.DateTime, 20);
                        Jaildate1.Direction = ParameterDirection.Input;
                        SqlParameter Jaildate2 = new SqlParameter("@Jaildate2", SqlDbType.DateTime, 20);
                        Jaildate2.Direction = ParameterDirection.Input;
                        if ((DtpJaildate1.Checked == true) && (DtpJaildate2.Checked == true))
                        {
                            Jaildate1.Value =
                                string.Format(
                                "{0}-{1}-{2} 00:00:00.000",
                                DtpJaildate1.Value.Year,
                                DtpJaildate1.Value.Month,
                                DtpJaildate1.Value.Day
                                );
                            Jaildate2.Value =
                                string.Format(
                                "{0}-{1}-{2} 23:59:59.999",
                                DtpJaildate2.Value.Year,
                                DtpJaildate2.Value.Month,
                                DtpJaildate2.Value.Day
                                );
                        }
                        else if ((DtpJaildate1.Checked == true))
                        {
                            Jaildate1.Value =
                                string.Format(
                                "{0}-{1}-{2} 00:00:00.000",
                                DtpJaildate1.Value.Year,
                                DtpJaildate1.Value.Month,
                                DtpJaildate1.Value.Day
                                );
                            Jaildate2.Value =
                                string.Format(
                                "{0}-{1}-{2} 23:59:59.999",
                                DtpJaildate1.Value.Year,
                                DtpJaildate1.Value.Month,
                                DtpJaildate1.Value.Day
                                );
                        }
                        else if ((DtpJaildate2.Checked == true))
                        {
                            Jaildate1.Value =
                              string.Format(
                              "{0}-{1}-{2} 00:00:00.000",
                              DtpJaildate2.Value.Year,
                              DtpJaildate2.Value.Month,
                              DtpJaildate2.Value.Day
                              );
                            Jaildate2.Value =
                                string.Format(
                                "{0}-{1}-{2} 23:59:59.999",
                                DtpJaildate2.Value.Year,
                                DtpJaildate2.Value.Month,
                                DtpJaildate2.Value.Day
                                );
                        }
                        cmd.Parameters.Add(Jaildate1);
                        cmd.Parameters.Add(Jaildate2);
                    }

                    #endregion

                    #region Registrationdate日期參數

                    if ((DtpRegistrationdate1.Checked == true) || (DtpRegistrationdate2.Checked == true))
                    {
                        SqlParameter Registration_date1 = new SqlParameter("@Registration_date1", SqlDbType.DateTime, 20);
                        Registration_date1.Direction = ParameterDirection.Input;
                        SqlParameter Registration_date2 = new SqlParameter("@Registration_date2", SqlDbType.DateTime, 20);
                        Registration_date2.Direction = ParameterDirection.Input;
                        if ((DtpRegistrationdate1.Checked == true) && (DtpRegistrationdate2.Checked == true))
                        {
                            Registration_date1.Value =
                                string.Format(
                                "{0}-{1}-{2} 00:00:00.000",
                                DtpRegistrationdate1.Value.Year,
                                DtpRegistrationdate1.Value.Month,
                                DtpRegistrationdate1.Value.Day
                                );
                            Registration_date2.Value =
                                string.Format(
                                "{0}-{1}-{2} 23:59:59.999",
                                DtpRegistrationdate2.Value.Year,
                                DtpRegistrationdate2.Value.Month,
                                DtpRegistrationdate2.Value.Day
                                );
                        }
                        else if ((DtpRegistrationdate1.Checked == true))
                        {
                            Registration_date1.Value =
                                string.Format(
                                "{0}-{1}-{2} 00:00:00.000",
                                DtpRegistrationdate1.Value.Year,
                                DtpRegistrationdate1.Value.Month,
                                DtpRegistrationdate1.Value.Day
                                );
                            Registration_date2.Value =
                                string.Format(
                                "{0}-{1}-{2} 23:59:59.999",
                                DtpRegistrationdate1.Value.Year,
                                DtpRegistrationdate1.Value.Month,
                                DtpRegistrationdate1.Value.Day
                                );
                        }
                        else if ((DtpRegistrationdate2.Checked == true))
                        {
                            Registration_date1.Value =
                              string.Format(
                              "{0}-{1}-{2} 00:00:00.000",
                              DtpRegistrationdate2.Value.Year,
                              DtpRegistrationdate2.Value.Month,
                              DtpRegistrationdate2.Value.Day
                              );
                            Registration_date2.Value =
                                string.Format(
                                "{0}-{1}-{2} 23:59:59.999",
                                DtpRegistrationdate2.Value.Year,
                                DtpRegistrationdate2.Value.Month,
                                DtpRegistrationdate2.Value.Day
                                );
                        }
                        cmd.Parameters.Add(Registration_date1);
                        cmd.Parameters.Add(Registration_date2);
                    }

                    #endregion

                    #endregion

                    #region 執行
                    conn.Open();
                    try
                    {
                        SqlDataReader dr = cmd.ExecuteReader();
                        int i = 1;
                        while (dr.Read())
                        {
                            #region 顯示轉換
                            string sexstring = "";
                            switch (Convert.ToString(dr["user_sex"]))
                            {
                                case "F":
                                    sexstring = "女";
                                    break;
                                case "M":
                                    sexstring = "男";
                                    break;
                            }
                            string user_birthday_string = "";
                            if (!dr.IsDBNull(dr.GetOrdinal("user_birthday")))
                            {
                                user_birthday_string = Convert.ToDateTime(dr["user_birthday"]).ToString("yyyy-MM-dd");
                            }

                            string user_jail_date_string = "";
                            if (!dr.IsDBNull(dr.GetOrdinal("user_jail_date")))
                            {
                                DateTime user_jail_date = Convert.ToDateTime(dr["user_jail_date"]);
                                user_jail_date_string =
                                string.Format(user_jail_date.ToString("yyyy-MM-dd HH:mm"));
                            }
                            DateTime user_registration_date = Convert.ToDateTime(dr["user_registration_date"]);
                            string user_registration_date_string =
                            string.Format(user_registration_date.ToString("yyyy-MM-dd HH:mm"));
                            #endregion
                            string[] list = {
                                   i.ToString(),
                                   Convert.ToString(dr["user_username"]),
                                   Convert.ToString(dr["user_first_name"]),
                                   Convert.ToString(dr["user_last_name"]),
                                   sexstring,
                                   user_birthday_string,
                                   Convert.ToString(dr["user_mobile_phone"]),
                                   Convert.ToString(dr["user_email"]),
                                   user_jail_date_string,
                                   user_registration_date_string
                               };
                            ListViewItem Item = new System.Windows.Forms.ListViewItem(list, -1);
                            Item.Tag = Convert.ToString(dr["user_id"]);
                            LvUserdata.Items.Add(Item);

                            i++;
                        }
                    }
                    catch { throw new Exception("查詢失敗,條件與上一次查詢有異"); }
                    cmd.Dispose();
                    conn.Close();
                    #endregion
                }
                catch (Exception ex) { MessageBox.Show(ex.Message); }
            }
        }

        private void CboPaging_SelectedIndexChanged(object sender, EventArgs e)
        {
            BtnSelect_Click(sender, e);
        }




        #region 按鈕樣式
        private void BtnFreeze_MouseEnter(object sender, EventArgs e)
        {
            this.BtnFreeze.FlatAppearance.BorderSize = 0;
            this.BtnFreeze.ForeColor = Color.White;
        }

        private void BtnFreeze_MouseLeave(object sender, EventArgs e)
        {
            this.BtnFreeze.FlatAppearance.BorderSize = 1;
            this.BtnFreeze.ForeColor = Color.Black;
        }

        private void btnThaw_MouseEnter(object sender, EventArgs e)
        {
            this.btnThaw.FlatAppearance.BorderSize = 0;
            this.btnThaw.ForeColor = Color.White;

        }

        private void btnThaw_MouseLeave(object sender, EventArgs e)
        {
            this.btnThaw.FlatAppearance.BorderSize = 1;
            this.btnThaw.ForeColor = Color.Black;
        }

        private void BtnSelect_MouseEnter(object sender, EventArgs e)
        {
            this.BtnSelect.FlatAppearance.BorderSize = 0;
            this.BtnSelect.ForeColor = Color.White;
        }

        private void BtnSelect_MouseLeave(object sender, EventArgs e)
        {
            this.BtnSelect.FlatAppearance.BorderSize = 1;
            this.BtnSelect.ForeColor = Color.Black;
        }

        private void BtnDelete_MouseEnter(object sender, EventArgs e)
        {
            this.BtnDelete.FlatAppearance.BorderSize = 0;
            this.BtnDelete.ForeColor = Color.White;
        }

        private void BtnDelete_MouseLeave(object sender, EventArgs e)
        {
            this.BtnDelete.FlatAppearance.BorderSize = 1;
            this.BtnDelete.ForeColor = Color.Black;
        }
        #endregion
    }
}
